CREATE PROCEDURE sp_ExportTable
	@TableName SYSNAME=NULL,
	@WhereClause SYSNAME=NULL
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @TableID INT;
	DECLARE @ColumnCount INT;
	DECLARE @ColumnLoop INT;
	DECLARE @ColumnName SYSNAME;
	DECLARE @ColumnType SYSNAME;
	DECLARE @IsNullable BIT;
	DECLARE @SQL NVARCHAR(MAX);
	CREATE TABLE #Output
	(
		SortOrder INT IDENTITY(1,1),
		Data NVARCHAR(MAX)
		PRIMARY KEY(SortOrder)
	);
    DECLARE @Columns TABLE
	(
		ID INT IDENTITY(1,1),
		ColumnName SYSNAME,
		ColumnType SYSNAME,
		IsNullable BIT,
		PRIMARY KEY(ID)
	);

	IF LEN(ISNULL(@TableName,'')) = 0
	BEGIN
		INSERT INTO #Output (Data)	VALUES 
		('sp_ExportTable TableName,WhereClause'),
		('Exports TableName.'),
		('WhereClause is optional'),
		('Reads TableName and'),
		('outputs a sql script'),
		('that will insert the'),
		('records into the table.'),
		('There''s a limit of 1000'),
		('records per export.');

		SELECT Data FROM #Output
		ORDER BY SortOrder;
		DROP TABLE #Output;
		RETURN;
	END;
	
	SELECT @TableID = id
	FROM sysobjects
	WHERE name = @TableName
	AND xtype='u';

	IF ISNULL(@TableID,0) = 0
	BEGIN
		SELECT 'Table ' + @TableName + ' wasn''t found.' As Error;
		DROP TABLE #Output;
		RETURN;
	END;

	-- Get the columns.
	INSERT INTO @Columns (ColumnName, ColumnType,IsNullable)
	SELECT SC.name,ST.name,SC.isnullable
	FROM syscolumns AS SC
	INNER JOIN systypes AS ST
	ON SC.xtype = ST.xtype
	WHERE SC.id = @TableID
	AND SC.iscomputed = 0
	AND ST.name <> 'sysname'
	ORDER BY SC.colid;

	SET @ColumnCount = @@ROWCOUNT;

	INSERT INTO #Output (Data) VALUES (N'SET IDENTITY_INSERT [' + @TableName + N'] ON;');
	INSERT INTO #Output (Data) VALUES (N'INSERT INTO [' + @TableName + N'] (');	
	SET @SQL = 'INSERT INTO #Output (Data) SELECT N''(''';
	
	--INSERT INTO @Output (Data) 
	SET @ColumnLoop = 1;
	WHILE @ColumnLoop <= @ColumnCount
	BEGIN
		SELECT @ColumnName = ColumnName,
		@ColumnType = ColumnType,
		@IsNullable = IsNullable
		FROM @Columns
		WHERE ID = @ColumnLoop;
		
		IF @ColumnLoop < @ColumnCount
		BEGIN
			INSERT INTO #Output (Data) VALUES (N'[' + @ColumnName + N'],');			
		END ELSE
		BEGIN
			INSERT INTO #Output (Data) VALUES (N'[' + @ColumnName + N']');			
		END

		IF @IsNullable = 0
		BEGIN
			SET @SQL = @SQL + CASE 
			WHEN @ColumnType IN ('int','float','bit','decimal','money','numeric') THEN '+CONVERT(NVARCHAR,[' + @ColumnName + N'])'
			WHEN @ColumnType IN ('varchar','nvarchar','text','ntext','char','nchar') THEN N'+''''''''+REPLACE(['+@ColumnName+N'],'''''''','''''''''''')+N'''''''''
			WHEN @ColumnType = 'datetime' THEN N'+''''''''+CONVERT(NVARCHAR,[' + @ColumnName + N'],120)+N'''''''''
			WHEN @ColumnType = 'uniqueidentifier' THEN N'+''''''''+CONVERT(NVARCHAR(50),[' + @ColumnName + N'])+N'''''''''
			ELSE N'Field type ' + @ColumnType + N' not supported for column ' + @ColumnName END;
		END ELSE
		BEGIN
			SET @SQL = @SQL + CASE 
			WHEN @ColumnType IN ('int','float','bit','decimal','money') THEN '+ISNULL(CONVERT(NVARCHAR,[' + @ColumnName + N']),''NULL'')'
			WHEN @ColumnType IN ('varchar','nvarchar','text','ntext') THEN N'+ISNULL(''''''''+REPLACE(['+@ColumnName+N'],'''''''','''''''''''')+N'''''''',''NULL'')'
			WHEN @ColumnType IN ('datetime','smalldatetime') THEN N'+ISNULL(''''''''+CONVERT(NVARCHAR,[' + @ColumnName + N'],120)+N'''''''',''NULL'')'
			WHEN @ColumnType = 'uniqueidentifier' THEN N'+ISNULL(''''''''+CONVERT(NVARCHAR(50),[' + @ColumnName + N'])+N'''''''',''NULL'')'
			ELSE N'Field type ' + @ColumnType + N' not supported for column ' + @ColumnName END;
		END;
				
		IF @ColumnLoop < @ColumnCount
		BEGIN
			SET @SQL = @SQL + N'+'',''';
		END

		SET @ColumnLoop  = @ColumnLoop  + 1;
	END;
	INSERT INTO #Output (Data) VALUES (N') VALUES');

	SET @SQL = @SQL + N'+''),'' AS Data FROM [' + @TableName + N']';
	IF LEN(ISNULL(@WhereClause,'')) > 0
	BEGIN
		SET @SQL = @SQL + N' WHERE ' + @WhereClause;
	END;
	SET @SQL = @SQL + N';';

	IF CHARINDEX('not supported for column',@SQL)>0
	BEGIN
		SELECT @SQL AS SQL;
	END ELSE
	BEGIN
		EXEC sp_executesql @SQL;

		-- Remove the extra comma.
		UPDATE #Output
		SET Data = SUBSTRING(Data,1,LEN(Data)-1)
		WHERE SortOrder = (SELECT MAX(SortOrder) FROM #Output);
			
		INSERT INTO #Output (Data) VALUES (N'SET IDENTITY_INSERT [' + @TableName + N'] OFF;');

		SELECT Data
		FROM #Output
		ORDER BY SortOrder;
	END

	DROP TABLE #Output;
	
END
GO
